package com.lxu.report.service;

import com.lxu.report.entity.YwReportData;
import com.lxu.report.entity.bo.ReportDirBO;
import com.lxu.report.entity.bo.ReportExpDataBO;
import com.lxu.report.entity.bo.ReportTableBO;
import com.lxu.report.entity.enums.ReportEnum;
import com.lxu.report.util.ReportToolsConstant;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.text.StrSubstitutor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * 模板数据组装 服务类
 */
@Service
public class TemplateDataAssembleService {

    @Autowired
    private TemplateQueryService templateQueryService;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private Logger logger = LoggerFactory.getLogger(TemplateDataAssembleService.class);


    public void generateTemplateData(ReportDirBO dir, Map<String, Object> templateTextMap, Map<String, List<List<String>>> templateTableMap, List<ReportTableBO> templateTables) {
        if (StringUtils.isNotBlank(dir.getDataSql())) {
            //执行数据集SQL脚本
            executeSql(templateTextMap, fillDynamicParameters(dir.getDataSql(),generateDynamicParameters(dir.getReportData())));
        }
        if (ReportEnum.DATA_TYPE.SINGLE_DATA.getValue().equals(dir.getDataType())) {
            logger.warn("该报告模板目录为单数据结构，无需进行导出数据配置");
            return;
        } else {
            List<ReportExpDataBO> exports = templateQueryService.queryDataByDir(dir.getId());
            if (CollectionUtils.isEmpty(exports)) {
                return;
            }
            for (ReportExpDataBO export : exports) {
                export.setDir(dir);
                executeSql(templateTextMap, templateTableMap, templateTables, export);
            }
        }
    }

    /**
     * 模板导出配置表中可能存在表单，也可能存在表格
     *
     * @param templateTextMap
     * @param templateTableMap
     * @param templateTables
     * @param export
     */
    private void executeSql(Map<String, Object> templateTextMap,
                            Map<String, List<List<String>>> templateTableMap,
                            List<ReportTableBO> templateTables,
                            ReportExpDataBO export) {
        //动态参数绑定
        String sql = fillDynamicParameters(export.getDataSql(),generateDynamicParameters(export.getDir().getReportData()));
        export.setDataSql(sql);
        //表单数据
        if (ReportEnum.DATA_TYPE.SINGLE_DATA.getValue().equals(export.getMetaType())) {
            executeSql(templateTextMap, export.getDataSql());
            //表格数据
        } else if (ReportEnum.DATA_TYPE.MULTIPLE_DATA.getValue().equals(export.getMetaType())) {
            Map<String, List<List<String>>> map = executeTableSql(export.getDataSql(), export.getTableIndex());
            if (map != null) {
                ReportTableBO reportTableBO = new ReportTableBO();
                reportTableBO.setTemplateTables(map);
                reportTableBO.setExportConfigEntity(export);
                templateTables.add(reportTableBO);
            }
            //多行表单
        } else {
            ReportTableBO reportTableBO = new ReportTableBO();
            reportTableBO.setMultipleFormFlag(true);
            reportTableBO.setRecordCount(executeMultipleFormSql(templateTextMap, export.getDataSql()));
            reportTableBO.setExportConfigEntity(export);
            templateTables.add(reportTableBO);
        }
    }

    /**
     * 多行表单复制
     *
     * @param templateTextMap
     * @param dataSql
     */
    private Integer executeMultipleFormSql(Map<String, Object> templateTextMap, String dataSql) {
        if (StringUtils.isBlank(dataSql)) {
            return null;
        }
        String[] sqlColumns = getSqlColumnsBySql(dataSql);

        List<Map<String, Object>> result = jdbcTemplate.queryForList(dataSql);
        if (result == null) {
            return null;
        }
        for (int i = 0; i < result.size(); i++) {
            Iterator iterator = result.get(i).entrySet().iterator();
            while (iterator.hasNext()) {
                Map.Entry entry = (Map.Entry) iterator.next();
                String suffix = i == 0 ? "" : i + "";
                templateTextMap.put(entry.getKey().toString() + suffix, entry.getValue());
            }
        }
        return result.size();
    }

    /**
     * 执行数据集sql,封装到table map中
     *
     * @param tableIndex
     * @param dataSql
     */
    private Map<String, List<List<String>>> executeTableSql(String dataSql, int tableIndex) {
        if (StringUtils.isBlank(dataSql)) {
            return null;
        }

        Map<String, List<List<String>>> templateTableMap = new HashMap<>();

        String[] sqlColumns = getSqlColumnsBySql(dataSql);


        List<Map<String, Object>> result = jdbcTemplate.queryForList(dataSql);
        if (result == null) {
            return null;
        }
        List<List<String>> values = new ArrayList<>();
        for (Map<String, Object> map : result) {
            List<String> list = new ArrayList<>();
            for (String sqlColumn : sqlColumns) {
                list.add(String.valueOf(map.get(sqlColumn)));
            }
            values.add(list);
        }
        templateTableMap.put(ReportToolsConstant.TABLE_PREFIX + tableIndex, values);
        return templateTableMap;
    }

    /**
     * 获取sql语句中的属性名，形成String[]
     *
     * @param dataSql
     * @return
     */
    private String[] getSqlColumnsBySql(String dataSql) {
        String columns = dataSql.substring(dataSql.toLowerCase().indexOf("select") + 6, dataSql.toLowerCase().indexOf("from")).trim();

        String[] sqlColumns = columns.split(",");

        for (int i = 0; i < sqlColumns.length; i++) {
            sqlColumns[i] = sqlColumns[i].trim().substring(2, sqlColumns[i].length());
        }
        return sqlColumns;
    }

    /**
     * 执行数据集sql，封装到占位符 map中
     *
     * @param templateTextMap
     * @param dataSql
     */
    private void executeSql(Map<String, Object> templateTextMap, String dataSql) {
        if (StringUtils.isBlank(dataSql)) {
            return;
        }
        List<Map<String, Object>> result = null;
        try {
            result = jdbcTemplate.queryForList(dataSql);
        } catch (Exception e) {
            throw new RuntimeException("数据查询为空,SQL如下：" + dataSql);
        }
        if(CollectionUtils.isEmpty(result) || result.get(0) ==null){
            return;
        }
        Iterator iterator = result.get(0).entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry entry = (Map.Entry) iterator.next();
            templateTextMap.put(String.valueOf(entry.getKey()), entry.getValue());
        }
    }

    /**
     * 用于 处理合作方这种表格整体复制情况的方法
     * @param dataSql
     * @param ywReportData
     * @return
     */
    public String executeSql(String dataSql,YwReportData ywReportData){
        if (StringUtils.isBlank(dataSql)) {
            return null;
        }
        dataSql = fillDynamicParameters(dataSql,generateDynamicParameters(ywReportData));
        return jdbcTemplate.queryForObject(dataSql,String.class);
    }

    /**
     * 对动态SQL进行填充，返回填充占位符的SQL语句
     * @param sql
     * @param tableId
     * @return
     */
    public String assembleDynamicSql(String sql,String tableId){
        Map<String, String> valuesMap = new HashMap<String, String>();
        valuesMap.put("tableId",tableId);
        return fillDynamicParameters(sql,valuesMap);
    }

    /**
     * 动态组装sql
     * @param dataSql
     * @return
     */
    private String fillDynamicParameters(String dataSql,Map<String, String> valuesMap) {
        if(StringUtils.isBlank(dataSql)){
            return dataSql;
        }
        StrSubstitutor sub = new StrSubstitutor(valuesMap);
        return sub.replace(dataSql);
    }

    /**
     * 获取动态参数，绑定到变量上
     * @param reportData
     * @return
     */
    private Map<String, String> generateDynamicParameters(YwReportData reportData){
        Map<String, String> valuesMap = new HashMap<String, String>();
        valuesMap.put("projectId", reportData.getProjectId());
        valuesMap.put("custId", reportData.getCustomerId());
        valuesMap.put("guarLetterId", reportData.getProjectId());//处理担保函sql动态参数问题
        valuesMap.put("contractDetailSignId",reportData.getProjectId());//处理合同及保费sql动态参数问题
        return valuesMap;
    }
}
